################### PROJECT- JCR REPLICATION - EM-DAT ##############################################
# This R file records the spatial information on the disaster-affected territories based on EM-DAT
# The recording of the affected territories is based on the raw data from EM-DAT - emdat_public_2022_10_18_query_uid-RK8Q95.csv
# Last updated: 16, May 2025, by Wangyin Zhao
##############################################################################################################

##############################################################################################################
# Outline of Script
# A. Preparation
#    - Set working directory
#    - Load required packages
# B. Import Data
# C. Compile the information on barangays affected by natural disasters based on EM-DAT 
##############################################################################################################

#### A. Preparation ####
# Set working directory to Replication_Files folder
setwd("./Replication_Files")

# Import relevant packages
packages <- c("tmap", "sf", "sp", "tidyverse", "zoo")
lapply(packages, library, character.only = TRUE)
########

#### B. Import data ####
# The spatial data on the barangays from the Philippine Standard Geographic Code (PSGC) 
bar <- st_read("./data/raw/Barangays/Barangays.shp") %>% st_make_valid()
########

#### C. Compile the information on barangays affected by natural disasters based on EM-DAT ####
# there are 73 natural disasters in the Philippines 
#
n1 <- bar %>% filter(ADM1_EN == "National Capital Region" | ADM1_EN == "Region V" |
                        ADM2_EN == "Bulacan" | ADM2_EN == "Zambales" | ADM2_EN == "Cebu" | ADM2_EN == "Easrern Samar" | ADM2_EN == "Leyte" | ADM2_EN == "Northern Samar" | ADM2_EN == "Samar")
n1$time <- 116
n1$year <- 201105
n2 <- bar %>% filter(ADM1_EN == "National Capital Region" | ADM1_EN == "Region III" | ADM1_EN == "Region IV-A" | ADM1_EN == "Region IV-B")
n2$time <- 115
n2$year <- 201106
n3 <- bar %>% filter(ADM1_EN == "National Capital Region" | ADM1_EN == "Region III" | 
                        ADM2_EN == "Pangasinan" | ADM2_EN == "Batangas" | ADM2_EN == "Cavite" | ADM2_EN == "Rizal"| ADM2_EN == "Albay"| ADM2_EN == "Cavite"| ADM2_EN == "Cavite"| ADM2_EN == "Camarines Sur")
n3$time <- 115
n3$year <- 201106
n4 <- bar %>% filter(ADM1_EN == "Region V" | 
                        ADM2_EN == "Nueva Ecija" | ADM2_EN == "Quezon" | ADM2_EN == "Cavite" | ADM2_EN == "Marinduque"| ADM2_EN == "Iloilo"| ADM2_EN == "Siquijor"| ADM2_EN == "Leyte"| ADM2_EN == "Ifugao" |
                        ADM3_EN == "PH137601000")
n4$time <- 114
n4$year <- 201107
n5 <- bar %>% filter(ADM3_PCODE == "PH137401000" | ADM3_PCODE == "PH137402000" | ADM3_PCODE == "PH137404000" | ADM3_PCODE == "PH137405000" | ADM3_PCODE == "PH035402000"| ADM3_PCODE == "PH036910000"| ADM3_PCODE == "PH041008000"| ADM3_PCODE == "PH041017000"| ADM3_PCODE == "PH045802000" |
                        ADM3_PCODE == "PH045808000" | ADM3_PCODE == "PH045811000")
n5$time <- 113
n5$year <- 201108
n6 <- bar %>% filter(ADM2_EN == "Bohol" | ADM2_EN == "Biliran" | ADM2_EN == "Eastern Samar" | ADM2_EN == "Samar" | ADM2_EN == "Southern Leyte" | ADM2_EN == "Lanao del Norte" | ADM2_EN == "Misamis Oriental" | ADM2_EN == "South Cotabato" | ADM2_EN == "Surigao del Sur" | ADM2_EN == "Leyte" | ADM2_EN == "Northern Samar")
n6$time <- 118
n6$year <- 201103
n7 <- bar %>% filter(ADM2_EN == "Southern Leyte" | ADM2_EN == "Camiguin" | ADM2_EN == "Misamis Occidental" | ADM2_EN == "Misamis Oriental" | ADM2_EN == "Davao Oriental" | ADM2_EN == "Agusan del Norte" | ADM2_EN == "Agusan del Sur" | ADM2_EN == "Dinagat Islands" | ADM2_EN == "Surigao del Norte" | ADM2_EN == "Surigao del Sur" | ADM2_EN == "Masbate" | ADM2_EN == "Sulu" | ADM2_EN == "Compostela Valley")
n7$time <- 119
n7$year <- 201102
n7.1 <- bar %>% filter(ADM2_EN == "Southern Leyte" | ADM2_EN == "Camiguin" | ADM2_EN == "Misamis Occidental" | ADM2_EN == "Misamis Oriental" | ADM2_EN == "Davao Oriental" | ADM2_EN == "Agusan del Norte" | ADM2_EN == "Agusan del Sur" | ADM2_EN == "Dinagat Islands" | ADM2_EN == "Surigao del Norte" | ADM2_EN == "Surigao del Sur" | ADM2_EN == "Masbate" | ADM2_EN == "Sulu" | ADM2_EN == "Compostela Valley")
n7.1$time <- 120
n7.1$year <- 201101
#### n7 for the month 1.25~2.7
n8 <- bar %>% filter(ADM2_EN == "Bukidnon" | ADM2_EN == "Davao del Sur" | ADM2_EN == "Cotabato" | ADM2_EN == "Sultan Kudarat" | ADM2_EN == "Maguindanao" | ADM2_EN == "Compostela Valley")
n8$time <- 115
n8$year <- 201106
n9 <- bar %>% filter(ADM1_EN == "Region X" | ADM1_EN == "Region XII" | 
                        ADM2_EN == "NCR, City of Manila, First District" | ADM2_EN == "Bulacan" | ADM2_EN == "Pampanga" | ADM2_EN == "Bataan"| ADM2_EN == "Tarlac"| ADM2_EN == "Zambales"| ADM2_EN == "Batangas"| ADM2_EN == "Albay" | ADM2_EN == "La Union" |ADM2_EN == "Pangasinan" |
                        ADM3_PCODE == "PH112402000" | ADM3_PCODE == "PH129804000")
n9$time <- 115
n9$year <- 201106
n10 <- bar %>% filter(ADM2_EN == "Sulu" | ADM2_EN == "Surigao del Norte" | ADM2_EN == "Agusan del Norte" | ADM2_EN == "Surigao del Sur" | ADM2_EN == "Agusan del Sur")
n10$time <- 119
n10$year <- 201102
n11 <- bar %>% filter(ADM4_PCODE == "PH063022026" | ADM4_PCODE == "PH063022078" |ADM4_PCODE == "PH063022045"|ADM4_PCODE == "PH063022086" | ADM4_PCODE == "PH063022030" | ADM4_PCODE == "PH063022171")
n11$time <- 113
n11$year <- 201108
n12 <- bar %>% filter(ADM3_PCODE == "PH118211000" )
n12$time <- 117
n12$year <- 201104
n13 <- bar %>% filter(ADM2_PCODE == "PH101300000" )
n13$time <- 114
n13$year <- 201107
n14 <- bar %>% filter(ADM4_PCODE == "PH037107001" )
n14$time <- 113
n14$year <- 201108
n15 <- bar %>% filter(ADM1_PCODE == "PH010000000" | 
                        ADM2_EN == "Zambales" | ADM2_EN == "Bulacan" | ADM2_EN == "Iloilo" | ADM2_EN == "Benguet"| ADM2_EN == "Abra"| ADM2_EN == "Mountain Province"| ADM2_EN == "Catanduanes"| ADM2_EN == "Zamboanga del Sur" |
                        ADM3_EN == "PH137404000")
n15$time <- 113
n15$year <- 201108
n16 <- bar %>% filter(ADM2_PCODE == "PH021500000" | ADM2_PCODE == "PH074600000" | ADM2_PCODE == "PH072200000" | ADM2_PCODE == "PH097200000" | ADM2_PCODE == "PH104300000"| ADM2_PCODE == "PH103500000"| ADM2_PCODE == "PH101300000"| ADM2_PCODE == "PH118200000"| ADM2_PCODE == "PH166800000"| ADM2_PCODE == "PH153600000")
n16$time <- 109
n16$year <- 201112
n17 <- bar %>% filter(ADM2_PCODE == "PH118200000")
n17$time <- 118
n17$year <- 201103
n18 <- bar %>% filter(ADM2_PCODE == "PH021500000" | ADM2_PCODE == "PH023100000" | ADM2_PCODE == "PH037700000" | ADM2_PCODE == "PH061900000" | ADM2_PCODE == "PH060400000")
n18$time <- 110
n18$year <- 201111
n19 <- bar %>% filter(ADM1_EN == "Cordillera Administrative Region" | ADM1_EN == "Region I" | ADM1_EN == "Region II" | ADM1_EN == "Region III")
n19$time <- 111
n19$year <- 201110
n20 <- bar %>% filter(ADM2_PCODE == "PH023100000" | ADM2_PCODE == "PH050500000" | ADM2_PCODE == "PH051600000" | ADM2_PCODE == "PH051700000" | ADM2_PCODE == "PH052000000"| ADM2_PCODE == "PH056200000"| ADM2_PCODE == "PH097300000"| ADM2_PCODE == "PH101300000"| ADM2_PCODE == "PH118200000"| ADM2_PCODE == "PH112300000" | ADM2_PCODE == "PH153600000"| ADM2_PCODE == "PH153800000" |
                         ADM3_EN == "PH137607000" )
n20$time <- 116
n20$year <- 201105
n21 <- bar %>% filter(ADM4_PCODE == "PH126312008" |ADM4_PCODE == "PH126312005"|ADM4_PCODE == "PH126312011"|ADM4_PCODE == "PH126312022")
n21$time <- 112
n21$year <- 201109
n22 <- bar %>% filter(ADM4_PCODE == "PH104311002" )
n22$time <- 111
n22$year <- 201110
n23 <- bar %>% filter(ADM3_PCODE == "PH112303000" | ADM3_PCODE == "PH112323000")
n23$time <- 110
n23$year <- 201111
n24 <- bar %>% filter(ADM3_PCODE == "PH064508000" | ADM3_PCODE == "PH064531000")
n24$time <- 116
n24$year <- 201105
n25 <- bar %>% filter(ADM3_PCODE == "PH097332000")
n25$time <- 114
n25$year <- 201107
n26 <- bar %>% filter(ADM2_PCODE == "PH05200000120" | ADM2_PCODE == "PH060400000" | ADM2_PCODE == "PH061900000" | ADM2_PCODE == "PH064500000" | ADM2_PCODE == "PH072200000" | ADM2_PCODE == "PH083700000"| ADM2_PCODE == "PH086400000"| ADM2_PCODE == "PH101300000"| ADM2_PCODE == "PH118200000"| ADM2_PCODE == "PH112300000"| ADM2_PCODE == "PH160300000"| ADM2_PCODE == "PH168500000" | ADM2_PCODE == "PH166700000"| ADM2_PCODE == "PH166800000")
n26$time <- 109
n26$year <- 201112
n27 <- bar %>% filter(ADM2_EN == "PH023100000" | ADM2_EN == "PH021500000" |
                         ADM3_PCODE == "PH037702000" | ADM3_PCODE == "PH037703000")
n27$time <- 109
n27$year <- 201112
n28 <- bar %>% filter(ADM1_EN == "Cordillera Administrative Region" | ADM1_EN == "National Capital Region" | ADM1_EN == "Region I" | ADM1_EN == "Region II" | ADM1_EN == "Region III" | ADM1_EN == "Region IV-A" | 
                         ADM2_PCODE == "PH175100000" | ADM2_PCODE == "PH175200000" | ADM2_PCODE == "PH175900000" | ADM2_PCODE == "PH050500000"| ADM2_PCODE == "PH051600000"| ADM2_PCODE == "PH051700000"| ADM2_PCODE == "PH052000000"| ADM2_PCODE == "PH060600000" |ADM2_PCODE == "PH063000000")
n28$time <- 112
n28$year <- 201109
n29 <- bar %>% filter(ADM2_PCODE == "PH175200000" | ADM2_PCODE == "PH175900000" | ADM2_PCODE == "PH061900000" | ADM2_PCODE == "PH063000000" | ADM2_PCODE == "PH064500000" | ADM2_PCODE == "PH072200000"| ADM2_PCODE == "PH086400000"| ADM2_PCODE == "PH104300000"| ADM2_PCODE == "PH126300000"| ADM2_PCODE == "PH168500000")
n29$time <- 111
n29$year <- 201110
n30 <- bar %>% filter(ADM1_EN == "Bangsamoro Autonomous Region in Muslim Mindanao" | ADM1_EN == "Cordillera Administrative Region" | ADM1_EN == "National Capital Region" | ADM1_EN == "Region I" | ADM1_EN == "Region II" | ADM1_EN == "Region III" | ADM1_EN == "Region IV-A" | ADM1_EN == "Region IV-B" | ADM1_EN == "Region IX" | ADM1_EN == "Region V" | ADM1_EN == "Region VI" | ADM1_EN == "Region VII" | ADM1_EN == "Region VIII" | ADM1_EN == "Region X" | ADM1_EN == "Region XI" | ADM1_EN == "Region XII" | ADM1_EN == "Region XIII")
n30$time <- 115
n30$year <- 201106
n31 <- bar %>% filter(ADM1_EN == "National Capital Region" | ADM1_EN == "Region I" | ADM1_EN == "Region III" | ADM1_EN == "Region IV-A" | ADM1_EN == "Region V" | ADM1_EN == "Region VI" | ADM1_EN == "Region VII" | ADM1_EN == "Region VIII" | ADM1_EN == "Region XIII" )
n31$time <- 99
n31$year <- 201210
n32 <- bar %>% filter(ADM1_EN == "Region IV-A" | ADM1_EN == "Region V" | ADM1_EN == "Region VI" | ADM1_EN == "Region VII" | ADM1_EN == "Region VIII" | ADM1_EN == "Region IV-B" )
n32$time <- 97
n32$year <- 201212
n33 <- bar %>% filter(ADM1_EN == "National Capital Region" | 
                         ADM2_EN == "Ilocos Sur" | ADM2_EN == "Pangasinan" | ADM2_EN == "Cagayan" | ADM2_EN == "Bataan"| ADM2_EN == "Bulacan"| ADM2_EN == "Nueva Ecija"| ADM2_EN == "Pampanga"| ADM2_EN == "Tarlac" | ADM2_EN == "Batangas" | ADM2_EN == "Cavite" | ADM2_EN == "Laguna" | ADM2_EN == "Rizal" | ADM2_EN == "Occidental Mindoro" | ADM2_EN == "Aklan" | ADM2_EN == "Antique" | ADM2_EN == "Iloilo" |  ADM2_EN == "Negros Occidental" | ADM2_EN == "Cebu" | ADM2_EN == "Lanao del Norte" | ADM2_EN == "Misamis Oriental" | ADM2_EN == "Cotabato"|ADM2_EN == "South Cotabato" |ADM2_EN == "Benguet" | ADM2_EN == "Ifugao"| ADM2_EN == "Kalinga"|ADM2_EN == "Mountain Province")
n33$time <- 101
n33$year <- 201208
n34 <- bar %>% filter(ADM2_PCODE == "PH013300000")
n34$time <- 101
n34$year <- 201208
n35 <- bar %>% filter(ADM2_PCODE == "PH097200000" |
                         ADM3_PCODE == "PH137603000")
n35$time <- 100
n35$year <- 201209
n36 <- bar %>% filter(ADM3_PCODE == "PH126505000")
n36$time <- 103
n36$year <- 201206
n37 <- bar %>% filter(ADM2_PCODE == "PH153800000" | ADM2_PCODE == "PH124700000" | ADM2_PCODE == "PH126500000" |
                         ADM3_PCODE == "PH128002000")
n37$time <- 103
n37$year <- 201206
n38 <- bar %>% filter(ADM1_EN == "Region IV-A" | 
                         ADM2_EN == "Agusan del Sur" | ADM2_EN == "Surigao del Sur" | ADM2_EN == "Leyte" | ADM2_EN == "Compostela Valley"| ADM2_EN == "Davao del Norte"| ADM2_EN == "Negros Occidental")
n38$time <- 108
n38$year <- 201201
n39 <- bar %>% filter(ADM3_PCODE == "PH160205000" | ADM3_PCODE == "PH103507000" | ADM3_PCODE == "PH103501000" )
n39$time <- 107
n39$year <- 201202
n40 <- bar %>% filter(ADM2_PCODE == "PH160200000" | ADM2_PCODE == "PH166700000" |
                         ADM3_PCODE == "PH126508000")
n40$time <- 107
n40$year <- 201202
n41 <- bar %>% filter(ADM2_PCODE == "PH133900000" | ADM2_PCODE == "PH012900000" | ADM2_PCODE == "PH013300000" | ADM2_PCODE == "PH015500000" | ADM2_PCODE == "PH030800000" | ADM2_PCODE == "PH031400000"| ADM2_PCODE == "PH034900000"| ADM2_PCODE == "PH035400000"| ADM2_PCODE == "PH036900000"| ADM2_PCODE == "PH037100000" | ADM2_PCODE == "PH041000000" | ADM2_PCODE == "PH042100000" | ADM2_PCODE == "PH043400000" | ADM2_PCODE == "PH045800000" | ADM2_PCODE == "PH175100000" | ADM2_PCODE == "PH175200000" | ADM2_PCODE == "PH063000000")
n41$time <- 101
n41$year <- 201208
n42 <- bar %>% filter(ADM1_PCODE == "PH060000000")
n42$time <- 106
n42$year <- 201203
n43 <- bar %>% filter(ADM1_PCODE == "PH160000000" | ADM1_PCODE == "PH150000000" | ADM1_PCODE == "PH090000000" | ADM1_PCODE == "PH100000000" | ADM1_PCODE == "PH110000000" | ADM1_PCODE == "PH120000000")
n43$time <- 103
n43$year <- 201206
n44 <- bar %>% filter(ADM4_PCODE == "PH118211007")
n44$time <- 108
n44$year <- 201201
n45 <- bar %>% filter(ADM1_PCODE == "PH140000000" | ADM1_PCODE == "PH130000000" | ADM1_PCODE == "PH010000000" | ADM1_PCODE == "PH020000000" | ADM1_PCODE == "PH030000000" | ADM1_PCODE == "PH040000000")
n45$time <- 101
n45$year <- 201208
n46 <- bar %>% filter(ADM1_PCODE == "PH130000000" | ADM1_PCODE == "PH030000000")
n46$time <- 100
n46$year <- 201209
n47 <- bar %>% filter(ADM2_PCODE == "PH037100000" | ADM2_PCODE == "PH175100000")
n47$time <- 99
n47$year <- 201210
n48 <- bar %>% filter(ADM1_PCODE == "PH110000000")
n48$time <- 97
n48$year <- 201212
n49 <- bar %>% filter(ADM1_PCODE == "PH140000000" | ADM1_PCODE == "PH010000000" | ADM1_PCODE == "PH020000000" | ADM1_PCODE == "PH030000000" | ADM1_PCODE == "PH040000000" | ADM1_PCODE == "PH050000000" | ADM1_PCODE == "PH060000000" | ADM1_PCODE == "PH170000000")
n49$time <- 88
n49$year <- 201309
n50 <- bar %>% filter(ADM1_PCODE == "PH100000000")
n50$time <- 96
n50$year <- 201301
n51 <- bar %>% filter(ADM2_PCODE == "PH036900000" | ADM2_PCODE == "PH030800000" | ADM2_PCODE == "PH043400000" | ADM2_PCODE == "PH175100000" | ADM2_PCODE == "PH015500000" | ADM2_PCODE == "PH140100000" |
                         ADM3_PCODE == "PH035408000" | ADM3_PCODE == "PH035406000" | ADM3_PCODE == "PH035414000" | ADM3_PCODE == "PH031412000" | ADM3_PCODE == "PH034905000" | ADM3_PCODE == "PH030807000" | ADM3_PCODE == "PH031410000" | ADM3_PCODE == "PH037107000" | ADM3_PCODE == "PH045814000" | ADM3_PCODE == "PH045804000" | ADM3_PCODE == "PH042109000" | ADM3_PCODE == "PH042103000" | ADM3_PCODE == "PH042111000" | ADM3_PCODE == "PH042116000" | ADM3_PCODE == "PH042120000" | ADM3_PCODE == "PH045645000" | ADM3_PCODE == "PH137601000" | ADM3_PCODE == "PH137501000" | ADM3_PCODE == "PH137504000" | ADM3_PCODE == "PH137503000" | ADM3_PCODE == "PH137502000" | ADM3_PCODE == "PH144404000" | ADM3_PCODE == "PH144409000" | ADM3_PCODE == "PH141113000")
n51$time <- 89
n51$year <- 201308
n52 <- bar %>% filter(ADM1_PCODE == "PH160000000" | ADM1_PCODE == "PH150000000" | ADM1_PCODE == "PH090000000" | ADM1_PCODE == "PH100000000" | ADM1_PCODE == "PH110000000" | ADM1_PCODE == "PH120000000")
n52$time <- 89
n52$year <- 201308
n53 <- bar %>% filter(ADM2_PCODE == "PH175300000" | ADM2_PCODE == "PH175200000"| ADM2_PCODE == "PH097200000" | ADM2_PCODE == "PH118200000"| ADM2_PCODE == "PH112300000")
n53$time <- 96
n53$year <- 201301
n54 <- bar %>% filter(ADM1_PCODE == "PH030000000" |
                         ADM2_PCODE == "PH025000000" | ADM2_PCODE == "PH015500000")
n54$time <- 87
n54$year <- 201310
n55 <- bar %>% filter(ADM2_PCODE == "PH012800000" | ADM2_PCODE == "PH021500000" | ADM2_PCODE == "PH148100000")
n55$time <- 87
n55$year <- 201310
n56 <- bar %>% filter(ADM2_PCODE == "PH082600000" | ADM2_PCODE == "PH086000000" | ADM2_PCODE == "PH083700000" | ADM2_PCODE == "PH072200000" | ADM2_PCODE == "PH063000000" | ADM2_PCODE == "PH061900000" | ADM2_PCODE == "PH060400000" | ADM2_PCODE == "PH175300000")
n56$time <- 86
n56$year <- 201311
# for n56, i add eastern Samar where the typhoon haiyan landed into the influence zone
n57 <- bar %>% filter(ADM1_PCODE == "PH150000000" | ADM1_PCODE == "PH060000000" | ADM1_PCODE == "PH070000000" | ADM1_PCODE == "PH080000000" | ADM1_PCODE == "PH090000000" | ADM1_PCODE == "PH100000000" | ADM1_PCODE == "PH110000000" | ADM1_PCODE == "PH120000000" | ADM1_PCODE == "PH160000000" |
                         ADM2_PCODE == "PH175300000")
n57$time <- 87
n57$year <- 201310
n58 <- bar %>% filter(ADM3_PCODE == "PH037107000" | ADM3_PCODE == "PH175307000")
n58$time <- 88
n58$year <- 201309
n59 <- bar %>% filter(ADM1_PCODE == "PH030000000" | ADM1_PCODE == "PH060000000" | ADM1_PCODE == "PH070000000" | ADM1_PCODE == "PH080000000")
n59$time <- 91
n59$year <- 201306
n60 <- bar %>% filter(ADM1_PCODE == "PH010000000" | ADM1_PCODE == "PH020000000" | ADM1_PCODE == "PH030000000")
n60$time <- 89
n60$year <- 201308
n61 <- bar %>% filter(ADM1_PCODE == "PH070000000" | ADM1_PCODE == "PH110000000" |
                         ADM2_PCODE == "PH175200000" | ADM2_PCODE == "PH083700000" | ADM2_PCODE == "PH097200000" | ADM2_PCODE == "PH101300000" | ADM2_PCODE == "PH103500000" | ADM2_PCODE == "PH124700000" | ADM2_PCODE == "PH126300000" | ADM2_PCODE == "PH160200000" | ADM2_PCODE == "PH153800000")
n61$time <- 95
n61$year <- 201302
n62 <- bar %>% filter(ADM1_PCODE == "PH150000000" | ADM1_PCODE == "PH100000000" | ADM1_PCODE == "PH110000000" | ADM1_PCODE == "PH160000000" |
                         ADM2_PCODE == "PH124700000" | ADM2_PCODE == "PH126300000")
n62$time <- 84
n62$year <- 201401
n63 <- bar %>% filter(ADM1_PCODE == "PH020000000" | ADM1_PCODE == "PH040000000" | ADM1_PCODE == "PH170000000")
n63$time <- 78
n63$year <- 201407
n64 <- bar %>% filter(ADM2_PCODE == "PH060600000" | ADM2_PCODE == "PH064500000" | ADM2_PCODE == "PH072200000" | ADM2_PCODE == "PH087800000" | ADM2_PCODE == "PH083700000" | ADM2_PCODE == "PH086400000" | ADM2_PCODE == "PH160200000" | ADM2_PCODE == "PH168500000" | ADM2_PCODE == "PH166700000")
n64$time <- 84
n64$year <- 201401 
###n64 it begins from 1.31 and end at 2.1
n65 <- bar %>% filter(ADM1_PCODE == "PH130000000" | ADM1_PCODE == "PH030000000" | ADM1_PCODE == "PH040000000" | ADM1_PCODE == "PH170000000" | ADM1_PCODE == "PH050000000" | ADM1_PCODE == "PH080000000")
n65$time <- 78
n65$year <- 201407 
n66 <- bar %>% filter(ADM1_PCODE == "PH130000000" | ADM1_PCODE == "PH040000000" | ADM1_PCODE == "PH170000000" | ADM1_PCODE == "PH050000000" | ADM1_PCODE == "PH060000000" | ADM1_PCODE == "PH070000000" | ADM1_PCODE == "PH080000000" |
                         ADM2_PCODE == "PH030800000" | ADM2_PCODE == "PH160200000" | ADM2_PCODE == "PH160300000" | ADM2_PCODE == "PH166800000"| ADM2_PCODE == "PH166700000")
n66$time <- 73
n66$year <- 201412
n67 <- bar %>% filter(ADM1_PCODE == "PH130000000" | ADM1_PCODE == "PH030000000" |
                         ADM2_PCODE == "PH012800000" | ADM2_PCODE == "PH015500000" | ADM2_PCODE == "PH021500000" | ADM2_PCODE == "PH023100000"| ADM2_PCODE == "PH025000000" | ADM2_PCODE == "PH064500000" | ADM2_PCODE == "PH045800000" | ADM2_PCODE == "PH148100000" | ADM2_PCODE == "PH141100000" | ADM2_PCODE == "PH143200000" | ADM2_PCODE == "PH013300000")
n67$time <- 76
n67$year <- 201409
n68 <- bar %>% filter(ADM2_PCODE == "PH035400000" | ADM2_PCODE == "PH133900000" |
                         ADM3_PCODE == "PH015518000" | ADM3_PCODE == "PH015522000")
n68$time <- 76
n68$year <- 201409
n69 <- bar %>% filter(ADM3_PCODE == "PH104307000" | ADM3_PCODE == "PH104305000" | ADM3_PCODE == "PH104313000" | ADM3_PCODE == "PH104320000" | ADM3_PCODE == "PH104321000" | ADM3_PCODE == "PH101317000")
n69$time <- 76
n69$year <- 201409
n70 <- bar %>% filter(ADM1_PCODE == "PH160000000" |
                         ADM2_PCODE == "PH175300000" | ADM2_PCODE == "PH060600000" | ADM2_PCODE == "PH061900000" | ADM2_PCODE == "PH064500000"| ADM2_PCODE == "PH071200000" | ADM2_PCODE == "PH072200000" | ADM2_PCODE == "PH076100000" | ADM2_PCODE == "PH083700000" | ADM2_PCODE == "PH086000000" | ADM2_PCODE == "PH086400000" | ADM2_PCODE == "PH097300000" | ADM2_PCODE == "PH101300000" | ADM2_PCODE == "PH101800000" | ADM2_PCODE == "PH103500000" | ADM2_PCODE == "PH104300000" | ADM2_PCODE == "PH118200000" | ADM2_PCODE == "PH112300000" | ADM2_PCODE == "PH112500000")
n70$time <- 73
n70$year <- 201412
n71 <- bar %>% filter(ADM2_PCODE == "PH153800000" |
                         ADM3_PCODE == "PH124714000" | ADM3_PCODE == "PH124703000" | ADM3_PCODE == "PH124712000" | ADM3_PCODE == "PH153815000"| ADM3_PCODE == "PH153817000")
n71$time <- 77
n71$year <- 201408
n72 <- bar %>% filter(ADM2_PCODE == "PH175300000" | ADM2_PCODE == "PH064500000" | ADM2_PCODE == "PH071200000" | ADM2_PCODE == "PH072200000" | ADM2_PCODE == "PH074600000" | ADM2_PCODE == "PH086400000" | ADM2_PCODE == "PH104200000" | ADM2_PCODE == "PH104300000" | ADM2_PCODE == "PH112400000" | ADM2_PCODE == "PH168500000" | ADM2_PCODE == "PH166700000" | ADM2_PCODE == "PH166800000")
n72$time <- 74
n72$year <- 201411
n73 <- bar %>% filter(ADM2_PCODE == "PH153800000")
n73$time <- 79
n73$year <- 201406

x <- rbind(n1,n2,n3,n4,n5,n6,n7,n7.1,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20,n21,n22,n23,n24,n25,n26,n27,n28,n29,n30,
           n31,n32,n33,n34,n35,n36,n37,n38,n39,n40,n41,n42,n43,n44,n45,n46,n47,n48,n49,n50,n51,n52,
           n53,n54,n55,n56,n57,n58,n59,n60,n61,n62,n63,n64,n65,n66,n67,n68,n69,n70,
           n71,n72,n73)
x_1 <- x[,c(3,4,21,22)]
x_1 <- x_1 %>% mutate (month = as.integer(substr(year,5,6))) %>%
  mutate (year = substr(year,1,4)) %>%
  mutate (ccode = paste(ADM4_PCODE, time, sep = "_"),
          year = as.numeric(year)) %>%
  select(ADM3_PCODE, ADM4_PCODE, year, month) 

# x_1 is the final one 
########
